using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using DTO;

namespace DAO
{
    //public class DuocPhamDao:IDaoObject<DuocPhamDto,int>
    public class DuocPhamDao
    {
        public DuocPhamDto SaveOrUpdate(DuocPhamDto obj)
        {
            return SaveOrUpdate(obj, null);
        }

        public DuocPhamDto SaveOrUpdate(DuocPhamDto obj, OleDbTransaction transaction)
        {
            //OleDbConnection cn = MyConnection.ConnectionData();
            OleDbCommand cmd = new OleDbCommand();
            if (transaction != null)
            {
                cmd.Connection = transaction.Connection;
                cmd.Transaction = transaction;
            }else
            {
                cmd.Connection = MyConnection.ConnectionData();
            }
                
            //cmd.Connection = cn;
            string sqlPattern = "";
            string sqlString = "";
            try
            {
                //if (obj.Id > 0)
                //{
                //    //update
                //    sqlPattern = "Update Duocpham Set Maduocpham = '{0}', Tenduocpham = '{1}',Noisanxuat='{3}',MaCtynhaphang='{4}',Solo='{5}',Madonvi='{6}',Maloaiduocpham='{7}',Gianhap='{8}',Giaxuat='{9}',Soluongtoithieu='{10}',Soluongtonkho='{11}'";
                //}
                //else
                //{
                    #region Insert into Ctynhaphang table
                    CongTyNhapHangDao congTyNhapHangDao = new CongTyNhapHangDao();
                    obj.CongTyNhapHang = congTyNhapHangDao.SaveOrUpdate(obj.CongTyNhapHang, transaction);
                    #endregion

                    #region Insert into Maloaiduocpham table
                    LoaiduocphamDao loaiduocphamDao = new LoaiduocphamDao();
                    obj.LoaiDuocPham = loaiduocphamDao.SaveOrUpdate(obj.LoaiDuocPham, transaction);
                    #endregion

                    #region Insert into Maloaiduocpham table
                    DonViDuocPhamDao donViDuocPhamDao = new DonViDuocPhamDao();
                    obj.DonViDuocPham = donViDuocPhamDao.SaveOrUpdate(obj.DonViDuocPham, transaction);
                    #endregion

                    sqlPattern = "Insert into Duocpham (Maduocpham, Tenduocpham,Noisanxuat,MaCtynhaphang,Madonvi,Maloaiduocpham,Gianhap,Giaxuat,Soluongtoithieu,Soluongtonkho)" +
                        "  values ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')";
                    sqlString = String.Format(sqlPattern, obj.MaDuocPham, obj.TenDuocPham, obj.NoiSanXuat, obj.CongTyNhapHang.Id, obj.DonViDuocPham.Id
                        , obj.LoaiDuocPham.Id, obj.GiaNhap, obj.GiaXuat, obj.SoLuongToiThieu, obj.SoLuongTonKho);
                //}
                cmd.CommandText = sqlString;
                cmd.ExecuteNonQuery();
               // obj.Id = MyConnection.GetnewId(transaction);
                //lay Id lon nhat(Id cua doi tuong moi vua them vao)
                string strSql = "Select @@IDENTITY";
                cmd.CommandText = strSql;
                cmd.Transaction = transaction;
                obj.Id = (int)cmd.ExecuteScalar();
                return obj;
            }
            catch (Exception exception)
            {
                //if (transaction != null) transaction.Rollback();

                throw new Exception(exception.Message);
            }
        }

        public void Delete(int id)
        {
            OleDbConnection cn = MyConnection.ConnectionData();
            string strSql = "Delete From Duocpham Where ID='" + id + "'";
            OleDbCommand cmd = new OleDbCommand(strSql, cn);
            //cmd.Parameters.Add("@Ma", OleDbType.Integer);
            //cmd.Parameters["@Ma"].Value = manv;
            cmd.ExecuteNonQuery();
            cn.Close();
        }
        
        public DuocPhamDto Select(int id)
        {
            OleDbConnection cn = MyConnection.ConnectionData();
            string strSql = "select dp.*,ct.Tencongty,ldp.Tenloaiduocpham,dv.Tendonvi from Duocpham dp,Ctynhaphang ct,Loaiduocpham ldp,Donviduocpham dv" +
                " where dp.Madonvi = dv.ID and dp.MaCtynhaphang= ct.ID and dp.Maloaiduocpham=ldp.ID and dp.ID=" + id + "";

            //string strSql = "select * from Duocpham where ID=" + id + "";
            OleDbCommand cmd = new OleDbCommand(strSql, cn);
            OleDbDataReader dr= cmd.ExecuteReader();
            DuocPhamDto dp = new DuocPhamDto();
            while (dr.Read())
            {

                dp.Id = Convert.ToInt32(dr["ID"].ToString());
                dp.MaDuocPham = dr["Maduocpham"].ToString();
                dp.TenDuocPham = dr["Tenduocpham"].ToString();
                dp.NoiSanXuat = dr["Noisanxuat"].ToString();
                CongTyNhapHangDto ctdto = new CongTyNhapHangDto();
                ctdto.Id = Convert.ToInt32(dr["MaCtynhaphang"].ToString());
                ctdto.TenCongTy = (string)dr["Tencongty"];
                dp.CongTyNhapHang = ctdto;
                //dp.SoLo = dr["Solo"].ToString();
                DonViDuocPhamDto dvdto = new DonViDuocPhamDto();
                dvdto.Id = Convert.ToInt32(dr["Madonvi"].ToString());
                dvdto.TenDonVi = (string)dr["Tendonvi"];
                dp.DonViDuocPham = dvdto;
                LoaiDuocPhamDto ldpdto = new LoaiDuocPhamDto();
                ldpdto.Id = Convert.ToInt32(dr["Maloaiduocpham"].ToString());
                ldpdto.TenLoaiDuocPham = (string)dr["Tenloaiduocpham"];
                dp.LoaiDuocPham = ldpdto;
                dp.GiaNhap = Convert.ToSingle(dr["Gianhap"].ToString());
                dp.GiaXuat = Convert.ToSingle(dr["Giaxuat"].ToString());
                dp.SoLuongToiThieu = Convert.ToInt32(dr["Soluongtoithieu"].ToString());
                dp.SoLuongTonKho = Convert.ToInt32(dr["Soluongtonkho"].ToString());
            }
            dr.Close();
            cn.Close();
            return dp;
        }

        public List<DuocPhamDto> SelectAll()
        {
            OleDbConnection cn = MyConnection.ConnectionData();
            string strSql = "select distinct dp.*,ct.Tencongty,ldp.Tenloaiduocpham,dv.Tendonvi from Duocpham dp,Ctynhaphang ct,Loaiduocpham ldp,Donviduocpham dv,Solo sl" +
               " where dp.Madonvi = dv.ID and dp.MaCtynhaphang= ct.ID and dp.Maloaiduocpham=ldp.ID"; 
            OleDbDataAdapter dta = new OleDbDataAdapter(strSql, cn);
            DataTable dataTable = new DataTable();
            dta.Fill(dataTable);
            List<DuocPhamDto> lst = new List<DuocPhamDto>();
            DuocPhamDto dp = new DuocPhamDto();
            foreach (DataRow dr in dataTable.Rows)
            {
                dp.Id = Convert.ToInt32(dr["ID"].ToString());
                dp.MaDuocPham = dr["Maduocpham"].ToString();
                dp.TenDuocPham = dr["Tenduocpham"].ToString();
                dp.NoiSanXuat = dr["Noisanxuat"].ToString();
                CongTyNhapHangDto ctdto = new CongTyNhapHangDto();
                ctdto.Id = Convert.ToInt32(dr["MaCtynhaphang"].ToString());
                ctdto.TenCongTy = (string)dr["Tencongty"];
                dp.CongTyNhapHang = ctdto;
                //dp.SoLo = dr["Solo"].ToString();
                DonViDuocPhamDto dvdto = new DonViDuocPhamDto();
                dvdto.Id = Convert.ToInt32(dr["Madonvi"].ToString());
                dvdto.TenDonVi = (string)dr["Tendonvi"];
                dp.DonViDuocPham = dvdto;
                LoaiDuocPhamDto ldpdto = new LoaiDuocPhamDto();
                ldpdto.Id = Convert.ToInt32(dr["Maloaiduocpham"].ToString());
                ldpdto.TenLoaiDuocPham = (string)dr["Tenloaiduocpham"];
                dp.LoaiDuocPham = ldpdto;
                dp.GiaNhap = Convert.ToSingle(dr["Gianhap"].ToString());
                dp.GiaXuat = Convert.ToSingle(dr["Giaxuat"].ToString());
                dp.SoLuongToiThieu = Convert.ToInt32(dr["Soluongtoithieu"].ToString());
                dp.SoLuongTonKho = Convert.ToInt32(dr["Soluongtonkho"].ToString());
                lst.Add(dp);
            }
            cn.Close();
            return lst;
        }
        /// <summary>
        /// create hash to save duocphamDto object with key is name and id is value
        /// </summary>
        /// <returns>hash table with key is name and id is value</returns>
        public static Hashtable SelectAllByHash()
        {
            OleDbConnection cn = MyConnection.ConnectionData();
            string strSql =
                "SELECT Duocpham.ID, Duocpham.Tenduocpham, Duocpham.Noisanxuat, Duocpham.MaCtynhaphang, Ctynhaphang.Tencongty, Duocpham.Madonvi, " +
                " Donviduocpham.Tendonvi, Duocpham.Maloaiduocpham, Loaiduocpham.Tenloaiduocpham, Solo.Solo, Duocpham.Gianhap, Duocpham.Giaxuat, Duocpham.Soluongtoithieu, Duocpham.Soluongtonkho " +
                " FROM ((Ctynhaphang INNER JOIN (Duocpham INNER JOIN Donviduocpham ON Duocpham.Madonvi = Donviduocpham.ID) ON Ctynhaphang.ID = Duocpham.MaCtynhaphang)" +
                " INNER JOIN Loaiduocpham ON Duocpham.Maloaiduocpham = Loaiduocpham.ID) INNER JOIN Solo ON Duocpham.ID = Solo.DuocphamID";
            OleDbDataAdapter dta = new OleDbDataAdapter(strSql, cn); 
            DataTable dataTable = new DataTable();
            dta.Fill(dataTable);
            Hashtable hashtableDp = new Hashtable();
            foreach (DataRow dr in dataTable.Rows)
            {
                DuocPhamDto dp = new DuocPhamDto();
                dp.Id = Convert.ToInt32(dr["ID"].ToString());
                //dp.MaDuocPham = dr["Maduocpham"].ToString();
                dp.SoLo = dr["Solo"].ToString();
                dp.TenDuocPham =  dr["Tenduocpham"].ToString().ToUpper();
                dp.NoiSanXuat = dr["Noisanxuat"].ToString();
                CongTyNhapHangDto ctdto = new CongTyNhapHangDto();
                ctdto.Id = Convert.ToInt32(dr["MaCtynhaphang"].ToString());
                ctdto.TenCongTy = (string)dr["Tencongty"];
                dp.CongTyNhapHang = ctdto;
                //dp.SoLo = dr["Solo"].ToString();
                DonViDuocPhamDto dvdto = new DonViDuocPhamDto();
                dvdto.Id = Convert.ToInt32(dr["Madonvi"].ToString());
                dvdto.TenDonVi = (string)dr["Tendonvi"];
                dp.DonViDuocPham = dvdto;
                LoaiDuocPhamDto ldpdto = new LoaiDuocPhamDto();
                ldpdto.Id = Convert.ToInt32(dr["Maloaiduocpham"].ToString());
                ldpdto.TenLoaiDuocPham = (string)dr["Tenloaiduocpham"];
                dp.LoaiDuocPham = ldpdto;
                dp.GiaNhap = Convert.ToSingle(dr["Gianhap"].ToString());
                dp.GiaXuat = Convert.ToSingle(dr["Giaxuat"].ToString());
                dp.SoLuongToiThieu = Convert.ToInt32(dr["Soluongtoithieu"].ToString());
                dp.SoLuongTonKho = Convert.ToInt32(dr["Soluongtonkho"].ToString());
                //hashtableDp.Add(dp.TenDuocPham + "-" + dp.MaDuocPham, dp);
                hashtableDp.Add(dp.TenDuocPham + "-" + dp.Id, dp);
            }
            cn.Close();
            return hashtableDp;
        }

        public DuocPhamDto[] ShowCurrentItems(DateTime fromDate, DateTime toDate, int loaihoadon)
        {
            OleDbConnection cn = MyConnection.ConnectionData();
            string strSql =
                "SELECT dp.*, Hoadon.Ngaytao, sl.Soluongnhap, sl.Hansudung, sl.Ngaysanxuat, " +
                "(dp.Gianhap*sl.Soluongnhap) AS Thanhtien,Ctynhaphang.*" +
                " FROM ((DuocPham AS dp INNER JOIN Solo AS sl ON dp.ID = sl.DuocphamID) " +
                " INNER JOIN Hoadon ON sl.HoadonmuaID = Hoadon.ID)  INNER JOIN Ctynhaphang ON dp.MaCtynhaphang = Ctynhaphang.ID" +
                " where Hoadon.Ngaytao >= DateSerial(" + fromDate.Year + "," + fromDate.Month + "," + fromDate.Day + ") and Hoadon.Ngaytao <= DateSerial(" + toDate.Year + "," + toDate.Month + "," + toDate.Day + ")";
            
            OleDbDataAdapter dta = new OleDbDataAdapter(strSql, cn);
            DataTable dataTable = new DataTable();
            dta.Fill(dataTable);
            List<DuocPhamDto> dpdto = new List<DuocPhamDto>();
            foreach (DataRow dr in dataTable.Rows)
            {
                DuocPhamDto dp = new DuocPhamDto();
                dp.Id = Convert.ToInt32(dr["dp.ID"].ToString());
                dp.MaDuocPham = dr["Maduocpham"].ToString();

                dp.CongTyNhapHang = new CongTyNhapHangDto();
                dp.CongTyNhapHang.Id = Convert.ToInt32(dr["Ctynhaphang.ID"].ToString());
                dp.CongTyNhapHang.TenCongTy = dr["Tencongty"].ToString();

                dp.TenDuocPham = dr["Tenduocpham"].ToString();
                dp.NoiSanXuat = dr["Noisanxuat"].ToString();
                dp.GiaNhap = Convert.ToSingle(dr["Gianhap"].ToString());
                dp.GiaXuat = Convert.ToSingle(dr["Giaxuat"].ToString());
                dp.SoLuongToiThieu = Convert.ToInt32(dr["Soluongtoithieu"].ToString());
                dp.SoLuongTonKho = Convert.ToInt32(dr["Soluongtonkho"].ToString());
                dp.SoLuongMua = Convert.ToInt32(dr["Soluongnhap"].ToString());
                dp.Ngaysanxuat = Convert.ToDateTime(dr["Ngaysanxuat"].ToString());
                dp.Hansudung = Convert.ToDateTime(dr["Hansudung"].ToString());
                dp.GiaNhap = Convert.ToSingle(dr["Gianhap"].ToString());
                dp.GiaXuat = Convert.ToSingle(dr["Giaxuat"].ToString());
                dp.NoiSanXuat = dr["Noisanxuat"].ToString();
                dp.ThanhTien = Convert.ToSingle( dr["Thanhtien"].ToString());
                dpdto.Add(dp);
            }
            cn.Close();
            return dpdto.ToArray();
        }

        public DuocPhamDto[] ShowSoldItems(DateTime fromDate, DateTime toDate)
        {
            OleDbConnection cn = MyConnection.ConnectionData();
            string strSql =
                "SELECT DISTINCT dp.Tenduocpham, sl.Hansudung, dp.Giaxuat, ([dp].[Giaxuat]*Soluongban) AS Thanhtien, dp.Noisanxuat, Sum(CtHinhthucban.Soluong) AS Soluongban,dp.ID " +
                " FROM (CtHinhthucban INNER JOIN (Hinhthucban INNER JOIN Hoadon ON Hinhthucban.HoadonID = Hoadon.ID) ON CtHinhthucban.HinhthucbanID = Hinhthucban.ID) INNER JOIN (DuocPham AS dp INNER JOIN Solo AS sl ON dp.ID = sl.DuocphamID) ON CtHinhthucban.DuocphamID = dp.ID" +
                " WHERE (((Hoadon.Ngaytao)>=DateSerial(" + fromDate.Year + "," + fromDate.Month + "," + fromDate.Day + ") And (Hoadon.Ngaytao)<=DateSerial(" + toDate.Year + "," + toDate.Month + "," + toDate.Day + ")) AND ((Hoadon.Loaihoadon)=0))" +
                " GROUP BY dp.Tenduocpham,  sl.Hansudung, dp.Giaxuat, dp.Noisanxuat,dp.ID" +
                " order by Sum(CtHinhthucban.Soluong) desc;";
            
            OleDbDataAdapter dta = new OleDbDataAdapter(strSql, cn);
            DataTable dataTable = new DataTable();
            dta.Fill(dataTable);
            List<DuocPhamDto> dpdto = new List<DuocPhamDto>();
            foreach (DataRow dr in dataTable.Rows)
            {
                DuocPhamDto dp = new DuocPhamDto();
                dp.Id = Convert.ToInt32(dr["ID"].ToString());

                dp.TenDuocPham = dr["Tenduocpham"].ToString();
                dp.NoiSanXuat = dr["Noisanxuat"].ToString();
                dp.GiaXuat = Convert.ToSingle(dr["Giaxuat"].ToString());
                dp.SoLuongBan = Convert.ToInt32(dr["Soluongban"].ToString());
                dp.Hansudung = Convert.ToDateTime(dr["Hansudung"].ToString());
                dp.ThanhTien = Convert.ToSingle(dr["Thanhtien"].ToString());
                dpdto.Add(dp);
            }
            cn.Close();
            return dpdto.ToArray();
        }

        public DuocPhamDto[] SelectHangganhet()
        {
            try
            {
                OleDbConnection cn = MyConnection.ConnectionData();
                string strSQL ="SELECT dp.Tenduocpham, dp.Soluongtonkho, dp.Noisanxuat,dp.Gianhap, sl.Hansudung FROM Duocpham dp, Solo sl WHERE sl.DuocphamID = dp.ID and dp.Soluongtonkho < dp.Soluongtoithieu ";
                OleDbCommand cmd = new OleDbCommand(strSQL, cn);
                OleDbDataReader dr;
                dr = cmd.ExecuteReader();
                List<DuocPhamDto> list = new List<DuocPhamDto>();
                while (dr.Read())
                {
                    DuocPhamDto dp = new DuocPhamDto();
                    int gianhap = Int32.Parse(dr["Gianhap"].ToString());
                    dp.TenDuocPham = dr["Tenduocpham"].ToString();
                    dp.NoiSanXuat = dr["Noisanxuat"].ToString();
                    dp.SoLuongTonKho = Convert.ToInt32(dr["Soluongtonkho"].ToString());
                    dp.Hansudung = Convert.ToDateTime(dr["Hansudung"].ToString());
                    dp.NoiSanXuat = dr["Noisanxuat"].ToString();
                    dp.ThanhTien = gianhap*dp.SoLuongTonKho;
                    list.Add(dp);
                }
                dr.Close();
                cn.Close();
                return list.ToArray();

            }
            catch (Exception exp)
            {

                throw new Exception(exp.ToString());
            }

        }


        public DuocPhamDto[] SelectHangcanngay(int soNgay)
        {
            try
            {
                DateTime now = DateTime.Now;
                OleDbConnection cn = MyConnection.ConnectionData();
                string strSql = "SELECT * from View_Hangcanngay " +
                                " where Hansudung - #" + now + "# <=" + soNgay +
                                " and  Hansudung - #" + now + "# >0 order by Hansudung";
                OleDbCommand cmd = new OleDbCommand(strSql, cn);
                OleDbDataReader dr = cmd.ExecuteReader();
                
                List<DuocPhamDto> list = new List<DuocPhamDto>();
                DateTime dtNow = DateTime.Now;
                while (dr.Read())
                {
                   
                        DuocPhamDto dp = new DuocPhamDto();
                        int gianhap = Int32.Parse(dr["Gianhap"].ToString());
                        dp.TenDuocPham = dr["Tenduocpham"].ToString();
                        dp.NoiSanXuat = dr["Noisanxuat"].ToString();
                        dp.SoLuongTonKho = Convert.ToInt32(dr["Soluongtonkho"].ToString());
                        dp.Hansudung = Convert.ToDateTime(dr["Hansudung"].ToString());
                        dp.NoiSanXuat = dr["Noisanxuat"].ToString();
                        dp.ThanhTien = gianhap * dp.SoLuongTonKho;
                        list.Add(dp);
                 
                }
                dr.Close();
                cn.Close();
                return list.ToArray();

            }
            catch (Exception exp)
            {

                throw new Exception(exp.ToString());
            }

        }

        public DuocPhamDto SelectName(string name)
        {
            OleDbConnection cn = MyConnection.ConnectionData();
            //string strSql = "select * from Duocpham where Tenduocpham=" + name + "";
            string strSql = "select dp.*,ct.Tencongty,ldp.Tenloaiduocpham,dv.Tendonvi from Duocpham dp,Ctynhaphang ct,Loaiduocpham ldp,Donviduocpham dv" +
                " where dp.Madonvi = dv.ID and dp.MaCtynhaphang= ct.ID and dp.Maloaiduocpham=ldp.ID and dp.Tenduocpham=" + name + "";

            //string strSql = "select * from Duocpham where ID=" + id + "";
            OleDbCommand cmd = new OleDbCommand(strSql, cn);
            OleDbDataReader dr = cmd.ExecuteReader();
            DuocPhamDto dp = new DuocPhamDto();
            while (dr.Read())
            {

                dp.Id = Convert.ToInt32(dr["ID"].ToString());
                dp.MaDuocPham = dr["Maduocpham"].ToString();
                dp.TenDuocPham = dr["Tenduocpham"].ToString();
                dp.NoiSanXuat = dr["Noisanxuat"].ToString();
                CongTyNhapHangDto ctdto = new CongTyNhapHangDto();
                ctdto.Id = Convert.ToInt32(dr["MaCtynhaphang"].ToString());
                ctdto.TenCongTy = (string)dr["Tencongty"];
                dp.CongTyNhapHang = ctdto;
                //dp.SoLo = dr["Solo"].ToString();
                DonViDuocPhamDto dvdto = new DonViDuocPhamDto();
                dvdto.Id = Convert.ToInt32(dr["Madonvi"].ToString());
                dvdto.TenDonVi = (string)dr["Tendonvi"];
                dp.DonViDuocPham = dvdto;
                LoaiDuocPhamDto ldpdto = new LoaiDuocPhamDto();
                ldpdto.Id = Convert.ToInt32(dr["Maloaiduocpham"].ToString());
                ldpdto.TenLoaiDuocPham = (string)dr["Tenloaiduocpham"];
                dp.LoaiDuocPham = ldpdto;
                dp.GiaNhap = Convert.ToSingle(dr["Gianhap"].ToString());
                dp.GiaXuat = Convert.ToSingle(dr["Giaxuat"].ToString());
                dp.SoLuongToiThieu = Convert.ToInt32(dr["Soluongtoithieu"].ToString());
                dp.SoLuongTonKho = Convert.ToInt32(dr["Soluongtonkho"].ToString());
            }
            dr.Close();
            cn.Close();
            return dp;
        }

        public static DuocPhamDto CapNhatSoLuongTonKho(DuocPhamDto obj)
        {
            OleDbConnection cn = MyConnection.ConnectionData();
            string strSQL = "Update Duocpham Set Soluongtonkho=? Where Id=?";
            OleDbCommand cmd = new OleDbCommand(strSQL, cn);
            cmd.Parameters.Add("@Soluongtonkho", OleDbType.Integer);
            cmd.Parameters["@Soluongtonkho"].Value = obj.SoLuongTonKho;
            cmd.Parameters.Add("@Id", OleDbType.Integer);
            cmd.Parameters["@Id"].Value = obj.Id;
            cmd.ExecuteNonQuery();
            cn.Close();
            return obj;
        }
    }
}
